1 import sqlite3
2
3 #making connection with database
4 def connect_database():
5 global conn
6 global cur
7 conn = sqlite3.connect("bankmanaging.db")
8
9 cur = conn.cursor()
10
11 cur.execute(
12 "create table if not exists bank (acc_no int, name text, age int, address text, balance int, account_type text, mobile_number int)")
13 cur.execute("create table if not exists staff (name text, pass text,salary int, position text)")
14 cur.execute("create table if not exists admin (name text, pass text)")
15 cur.execute("insert into admin values('arpit','123')")
16 conn.commit()
17 cur.execute("select acc_no from bank")
18 acc = cur.fetchall()
19 global acc_no
20 if len(acc) == 0:
21 acc_no = 1
22 else:
23 acc_no = int(acc[-1][0]) + 1
24 #check admin dtails in database
25 def check_admin(name,password):
26
27 cur.execute("select * from admin")
28 data=cur.fetchall()
29
30 if data[0][0]==name and data[0][1]==password:
31 return True
32 return
33 #create employee in database
34 def create_employee(name,password,salary,positon):
35 print(password)
36 cur.execute("insert into staff values(?,?,?,?)",(name,password,salary,positon))
37 conn.commit()
38 #check employee details in dabase for employee login
39 def check_employee(name,password):
40 print(password)
41 print(name)
42 cur.execute("select name,pass from staff")
43 data=cur.fetchall()
44 print(data)
45 if len(data)==0:
46 return False
47 for i in range(len(data)):
48 if data[i][0]==name and data[i][1]==password:
49 return True
50
51 return False
52 #create customer details in database
53 def create_customer(name,age,address,balance,acc_type,mobile_number):
54 global acc_no
55 cur.execute("insert into bank values(?,?,?,?,?,?,?)",(acc_no,name,age,address,balance,acc_type,mobile_number))
56 conn.commit()
57 acc_no=acc_no+1
58 return acc_no-1
59 #check account in database
60 def check_acc_no(acc_no):
61 cur.execute("select acc_no from bank")
62 list_acc_no=cur.fetchall()
63
64 for i in range(len(list_acc_no)):
65 if list_acc_no[i][0]==int(acc_no):
66 return True
67 return False
68 #get all details of a particular customer from database
69 def get_details(acc_no):
70 cur.execute("select * from bank where acc_no=?",(acc_no))
71 global detail
72 detail = cur.fetchall()
73 print(detail)
74 if len(detail)==0:
75 return False
76 else:
77 return (detail[0][0],detail[0][1],detail[0][2],detail[0][3],detail[0][4],detail[0][5],detail[0][6])
78 #add new balance of customer in bank database
79 def update_balance(new_money,acc_no):
80 cur.execute("select balance from bank where acc_no=?",(acc_no,))
81 bal=cur.fetchall()
82 bal=bal[0][0]
83 new_bal=bal+int(new_money)
84
85 cur.execute("update bank set balance=? where acc_no=?",(new_bal,acc_no))
86 conn.commit()
87 #deduct balance from customer bank database
88 def deduct_balance(new_money,acc_no):
89 cur.execute("select balance from bank where acc_no=?",(acc_no,))
90 bal=cur.fetchall()
91 bal=bal[0][0]
92 if bal<int(new_money):
93 return False
94 else:
95 new_bal=bal-int(new_money)
96
97 cur.execute("update bank set balance=? where acc_no=?",(new_bal,acc_no))
98 conn.commit()
99 return True
100 #gave balance of a particular account number from database
101 def check_balance(acc_no):
102 cur.execute("select balance from bank where acc_no=?",(acc_no))
103 bal=cur.fetchall()
104 return bal[0][0]
105 #update_name_in_bank_table
106 def update_name_in_bank_table(new_name,acc_no):
107 print(new_name)
108 conn.execute("update bank set name='{}' where acc_no={}".format(new_name,acc_no))
109 conn.commit()
110 #update_age_in_bank_table
111 def update_age_in_bank_table(new_name,acc_no):
112 print(new_name)
113 conn.execute("update bank set age={} where acc_no={}".format(new_name,acc_no))
114 conn.commit()
115 #update_address_in_bank_table
116 def update_address_in_bank_table(new_name,acc_no):
117 print(new_name)
118 conn.execute("update bank set address='{}' where acc_no={}".format(new_name,acc_no))
119 conn.commit()
120
121 #list of all customers in bank
122 def list_all_customers():
123 cur.execute("select * from bank")
124 deatil=cur.fetchall()
125
126 return deatil
127 #delete account from database
128 def delete_acc(acc_no):
129 cur.execute("delete from bank where acc_no=?",(acc_no))
130 conn.commit()
131 #show employees detail from staff table
132 def show_employees():
133 cur.execute("select name, salary, position,pass from staff")
134 detail=cur.fetchall()
135 return detail
136 #return all money in bank
137 def all_money():
138 cur.execute("select balance from bank")
139 bal=cur.fetchall()
140 print(bal)
141 if len(bal)==0:
142 return False
143 else:
144 total=0
145 for i in bal:
146
147 total=total+i[0]
148 return total
149 #return a list of all employees name
150 def show_employees_for_update():
151 cur.execute("select * from staff")
152 detail=cur.fetchall()
153 return detail
154 #update employee name from data base
155 def update_employee_name(new_name,old_name):
156 print(new_name,old_name)
157 cur.execute("update staff set name='{}' where name='{}'".format(new_name,old_name))
158 conn.commit()
159 def update_employee_password(new_pass,old_name):
160 print(new_pass,old_name)
161 cur.execute("update staff set pass='{}' where name='{}'".format(new_pass,old_name))
162 conn.commit()
163 def update_employee_salary(new_salary,old_name):
164 print(new_salary,old_name)
165 cur.execute("update staff set salary={} where name='{}'".format(new_salary,old_name))
166 conn.commit()
167 def update_employee_position(new_pos,old_name):
168 print(new_pos,old_name)
169 cur.execute("update staff set position='{}' where name='{}'".format(new_pos,old_name))
170 conn.commit()
171 #get name and balance from bank of a particular account number
172 def get_detail(acc_no):
173 cur.execute("select name, balance from bank where acc_no=?",(acc_no))
174 details=cur.fetchall()
175 return details
176 def check_name_in_staff(name):
177 cur=conn.cursor()
178 cur.execute("select name from staff")
179 details=cur.fetchall()
180
181 for i in details:
182 if i[0]==name:
183 return True
184 return False
